package com.decent.ejfadmin.user.dao.provider;

import com.decent.ejfadmin.common.bean.*;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;
import java.util.Objects;


/**
 * 会员信息
 *
 * @author weicx
 * @date 2018/07/25
 */
public class UserProvider {
    /**
     * 查询会员
     *
     * @param param 参数
     * @return sql
     */
    @SuppressWarnings({"unused", "UserDao.queryUserRecordPage"})
    public String queryUserRecordPage(HashMap<String, Object> param) {
        StringBuilder sql = new StringBuilder(100);
        sql.append("select administer, create_time, record from yuhuang_sup_user_record where user_id = #{userId} ORDER BY id desc ");
        Page page = (Page) param.get("page");
        sql.append(" LIMIT ").append(page.getBeginIndex()).append(" , ").append(page.getSize());
        return sql.toString();
    }

    /**
     * 查询会员数量
     *
     * @param param 参数
     * @return sql
     */
    public String querySupUserCount(HashMap<String, Object> param) {
        StringBuffer sql = new StringBuffer(300);
        sql.append("SELECT COUNT(1) from ( " +
                "   SELECT t.user_Id   ");
        commonSql(sql, param);
        sql.append(" )st ");
        return sql.toString();
    }

    /**
     * 查询会员
     *
     * @param param 参数
     * @return sql
     */
    public String querySupUser(HashMap<String, Object> param) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("SELECT " +
                " t.*, IFNULL(up.user_id, 0) isPrivatePrice ");
        commonSql(sql, param);
        Page page = (Page) param.get("page");
        sql.append(" ORDER BY t.user_Id desc ");
        sql.append(" LIMIT ").append(page.getBeginIndex()).append(" , ").append(page.getSize());
        return sql.toString();
    }

    private void commonSql(StringBuffer sql, HashMap<String, Object> param) {
        SupUser supUser = (SupUser) param.get("supUser");
        sql.append(" FROM ( SELECT u.account, u.api_state, u.notify_url, u.ip, u.login_Pwd, u.pay_Pwd, u.pm_id, " +
                "a.pm_name 'userPm.name', u.recommend_id, DATE_FORMAT(u.registe_time, '%Y-%m-%d %H:%i:%S') registeTime, " +
                "u.status, u.supply_model, m.model_name, u.type, u.equity_credit, u.equity_api_state, u.user_id, u.user_Key, " +
                "u.qb_large_amount_config, u.qb_large_amount, i.city 'supInfo.city', i.industry 'supInfo.industry', " +
                "i.`name` 'supInfo.name', i.nick_name 'supInfo.nickName', i.open_id 'supInfo.openId', i.qq 'supInfo.qq', " +
                "i.wangwang 'supInfo.wangwang', i.email 'supInfo.email', i.id_number 'supInfo.idNumber', " +
                "i.id_type 'supInfo.idType', i.remark 'supInfo.remark', i.real_name 'supInfo.realName', " +
                "i.address 'supInfo.address', i.phone 'supInfo.phone', u.submit_card_apply, u.daily_supply_limit, " +
                "u.entity_limit FROM yuhuang_sup_info i " +
                "INNER JOIN yuhuang_sup_user u ON i.user_id = u.user_id INNER JOIN yuhuang_sup_privilege p ON " +
                "i.user_id = p.user_id INNER JOIN yuhuang_supply_model m ON m.id = u.supply_model LEFT JOIN " +
                "yuhuang_pm a ON u.pm_id = a.pm_id WHERE 1 = 1 ");
        if (StringUtils.isNotBlank(supUser.getUserIds())) {
            sql.append(" and u.user_id in").append("(").append(supUser.getUserIds()).append(")");
        }
        String auditType = (String) param.get("auditType");
        if (StringUtils.isNotBlank(auditType)) {
            sql.append(" AND u.submit_card_apply = #{auditType} ");
        }
        //包含条件
        Object include = param.get("include");
        String startTime = (String) param.get("startTime");
        String endTime = (String) param.get("endTime");
        if (include.equals(0)) {
            if (StringUtils.isNotBlank(startTime)) {
                sql.append(" AND u.registe_time >=#{startTime}");
            }
            if (StringUtils.isNotBlank(endTime)) {
                sql.append(" AND u.registe_time <=#{endTime}");
            }
            if (StringUtils.isNotBlank(supUser.getAccount())) {
                sql.append(" AND (instr(u.account,#{supUser.account})>0 or instr(i.name,#{supUser.account})>0 )");
            }
            if (supUser.getPmId() != null) {
                sql.append(" AND u.pm_id=#{supUser.pmId}");
            }
            if (supUser.getStatus() != null) {
                sql.append(" AND u.`status`=#{supUser.status}");
            }
            if (supUser.getSupplyModel() != null) {
                sql.append(" AND u.supply_model=#{supUser.supplyModel}");
            }
            if (supUser.getType() != null) {
                sql.append(" AND u.type=#{supUser.type}");
            }
            if (supUser.getApiState() != null) {
                sql.append(" AND u.api_state=#{supUser.apiState}");
            }
            if (supUser.getSupInfo() != null) {
                if (supUser.getSupInfo().getIndustry() != null) {
                    sql.append(" AND i.industry=#{supUser.supInfo.industry}");
                }
                if (StringUtils.isNotBlank(supUser.getSupInfo().getIp())) {
                    sql.append(" AND i.ip=#{supUser.supInfo.ip}");
                }
            }
            if (supUser.getUserPm() != null) {
                if (StringUtils.isNotBlank(supUser.getUserPm().getName())) {
                    sql.append(" AND instr(a.pm_name,#{supUser.userPm.name})>0");
                }
            }
        } else {
            if (StringUtils.isNotBlank(startTime) && StringUtils.isBlank(endTime)) {
                sql.append(" AND u.registe_time <=#{startTime}");
            }
            if (StringUtils.isNotBlank(endTime) && StringUtils.isBlank(startTime)) {
                sql.append(" and u.registe_time >=#{endTime}");
            }
            if (StringUtils.isNotBlank(endTime) && StringUtils.isNotBlank(startTime)) {
                sql.append(" AND (u.registe_time <=#{startTime} or u.registe_time >=#{endTime})");
            }
            if (StringUtils.isNotBlank(supUser.getAccount())) {
                sql.append(" AND (instr(u.account,#{supUser.account})=0 and instr(i.name,#{supUser.account})=0 )");
            }
            if (supUser.getPmId() != null) {
                sql.append(" AND u.pm_id!=#{supUser.pmId}");
            }
            if (supUser.getStatus() != null) {
                sql.append(" AND u.`status`!=#{supUser.status}");
            }
            if (supUser.getSupplyModel() != null) {
                sql.append(" AND u.supply_model!=#{supUser.supplyModel}");
            }
            if (supUser.getType() != null) {
                sql.append(" AND u.type!=#{supUser.type}");
            }
            if (supUser.getApiState() != null) {
                sql.append(" AND u.api_state!=#{supUser.apiState}");
            }
            if (supUser.getSupInfo() != null) {
                if (supUser.getSupInfo().getIndustry() != null) {
                    sql.append(" AND i.industry!=#{supUser.supInfo.industry}");
                }
                if (StringUtils.isNotBlank(supUser.getSupInfo().getIp())) {
                    sql.append(" AND i.ip!=#{supUser.supInfo.ip}");
                }
            }
            if (supUser.getUserPm() != null) {
                if (StringUtils.isNotBlank(supUser.getUserPm().getName())) {
                    sql.append(" AND instr(a.pm_name,#{supUser.userPm.name})=0");
                }
            }
        }
        sql.append(" ) t LEFT JOIN yuhuang_user_product_price up ON up.user_id = t.user_Id GROUP BY t.user_Id ");
    }

    /**
     * 修改sup会员
     *
     * @param map 参数
     * @return sql
     */
    @SuppressWarnings("unused")
    public String updateUser(HashMap<String, SupUser> map) {
        StringBuilder sql = new StringBuilder(100);
        sql.append("update yuhuang_sup_user set user_id = user_id ");
        SupUser supUser = map.get("supUser");
        if (StringUtils.isNotBlank(supUser.getLoginPwd())) {
            sql.append("  ,login_Pwd = #{supUser.loginPwd} ");
        }
        if (StringUtils.isNotBlank(supUser.getPayPwd())) {
            sql.append("  ,pay_Pwd = #{supUser.payPwd} ");
        }
        if (supUser.getStatus() != null) {
            sql.append(" ,  status = #{supUser.status} ");
        }
        if (supUser.getType() != null) {
            sql.append(" ,  type = #{supUser.type} ");
        }
        if (supUser.getPmId() != null) {
            sql.append(" ,  pm_id = #{supUser.pmId} ");
        }
        if (supUser.getSupplyModel() != null) {
            sql.append(" ,  supply_model = #{supUser.supplyModel} ");
        }
        if (StringUtils.isNotBlank(supUser.getIp())) {
            sql.append(" ,  ip = #{supUser.ip} ");
        }
        if (supUser.getApiState() != null) {
            sql.append(" ,  api_state = #{supUser.apiState} ");
        }
        if (StringUtils.isNotBlank(supUser.getNotifyUrl())) {
            sql.append(" ,  notify_url = #{supUser.notifyUrl} ");
        }
        if (StringUtils.isNotBlank(supUser.getUserKey())) {
            sql.append(" ,  user_Key = #{supUser.userKey} ");
        }
        if (supUser.getDailySupplyLimit() != null) {
            sql.append(" ,  daily_supply_limit = #{supUser.dailySupplyLimit} ");
        }
        if (supUser.getEntityLimit() != null) {
            sql.append(" ,  entity_limit = #{supUser.entityLimit} ");
        }
        if (supUser.getEquityApiState() != null) {
            sql.append(" ,  equity_api_state = #{supUser.equityApiState} ");
        }
        sql.append("  where user_id = #{supUser.userId} ");
        return sql.toString();
    }

    /**
     * 修改会员权限
     *
     * @param params 参数
     * @return 返回sql
     */
    public String modifySupPrivilege(HashMap<String, SupPrivilege> params) {
        SupPrivilege privilege = params.get("supPrivilege");
        StringBuilder sql = new StringBuilder();
        sql.append(" UPDATE yuhuang_sup_privilege s " +
                "SET id = id ");
        if (Objects.nonNull(privilege.getBusinessOverview())) {
            sql.append("  ,s.business_overview =  #{supPrivilege.businessOverview} ");
        }
        if (Objects.nonNull(privilege.getPayPwdState())) {
            sql.append("  ,s.pay_pwd =  #{supPrivilege.payPwdState} ");
        }
        if (Objects.nonNull(privilege.getRecommend())) {
            sql.append("  ,s.business_overview =  #{supPrivilege.recommend} ");
        }
        if (Objects.nonNull(privilege.getStatisticReport())) {
            sql.append("  ,s.statistic_report =  #{supPrivilege.statisticReport} ");
        }
        if (Objects.nonNull(privilege.getVirtualOperate())) {
            sql.append("  ,s.virtual_operate =  #{supPrivilege.virtualOperate} ");
        }
        if (Objects.nonNull(privilege.getSupplyState())) {
            sql.append("  ,s.supply =  #{supPrivilege.supplyState} ");
        }
        if (Objects.nonNull(privilege.getPostPaid())) {
            sql.append("  ,s.post_paid =  #{supPrivilege.postPaid} ");
        }
        if (Objects.nonNull(privilege.getPopUp())) {
            sql.append("  ,s.pop_up =  #{supPrivilege.popUp} ");
        }
        if (Objects.nonNull(privilege.getAlipayWithdraw())) {
            sql.append("  ,s.alipay_withdraw =  #{supPrivilege.alipayWithdraw} ");
        }
        if (Objects.nonNull(privilege.getWithdrawState())) {
            sql.append("  ,s.withdraw_state =  #{supPrivilege.withdrawState} ");
        }
        if (Objects.nonNull(privilege.getQbExchange())) {
            sql.append("  ,s.qb_exchange =  #{supPrivilege.qbExchange} ");
        }
        sql.append(" WHERE s.user_id =  #{supPrivilege.userId}  ");
        return sql.toString();
    }

    /**
     * 修改会员Info信息
     *
     * @param map 参数
     * @return sql
     */
    @SuppressWarnings("unused")
    public String updateUserInfo(HashMap<String, SupInfo> map) {
        StringBuilder sql = new StringBuilder(100);
        SupInfo supInfo = map.get("supInfo");
        sql.append("update yuhuang_sup_info set user_id = user_id ");
        if (StringUtils.isNotBlank(supInfo.getPhone())) {
            sql.append(" ,  phone = #{supInfo.phone} ");
        }
        if (StringUtils.isNotBlank(supInfo.getName())) {
            sql.append(" ,  name = #{supInfo.name} ");
        }
        if (StringUtils.isNotBlank(supInfo.getOpenId())) {
            sql.append(" ,  open_id = #{supInfo.openId} ");
        }
        if (StringUtils.isNotBlank(supInfo.getNickName())) {
            sql.append(" ,  nick_name = #{supInfo.nickName} ");
        }
        if (StringUtils.isNotBlank(supInfo.getCity())) {
            sql.append(" ,  city = #{supInfo.city} ");
        }
        if (StringUtils.isNotBlank(supInfo.getIp())) {
            sql.append(" ,  ip = #{supInfo.ip} ");
        }
        if (StringUtils.isNotBlank(supInfo.getAddress())) {
            sql.append("  ,`address`=#{supInfo.address} ");
        }
        if (StringUtils.isNotBlank(supInfo.getQq())) {
            sql.append("  ,`qq`=#{supInfo.qq} ");
        }
        if (StringUtils.isNotBlank(supInfo.getWangwang())) {
            sql.append("  ,`wangwang`=#{supInfo.wangwang} ");
        }
        if (StringUtils.isNotBlank(supInfo.getEmail())) {
            sql.append("  ,`email`=#{supInfo.email} ");
        }
        if (StringUtils.isNotBlank(supInfo.getIdNumber())) {
            sql.append("  ,`id_number`=#{supInfo.idNumber} ");
        }
        if (StringUtils.isNotBlank(supInfo.getIdType())) {
            sql.append("  ,`id_type`=#{supInfo.idType} ");
        }
        if (StringUtils.isNotBlank(supInfo.getRemark())) {
            sql.append("  ,`remark`=#{supInfo.remark} ");
        }
        if (StringUtils.isNotBlank(supInfo.getRealName())) {
            sql.append("  ,`real_name`=#{supInfo.realName} ");
        }
        sql.append(" where user_id = #{supInfo.userId} ");
        return sql.toString();
    }

    /**
     * 创建用户信息
     *
     * @param map 参数集
     * @return 返回sql语句
     */
    public String createSupUserInfo(HashMap<String, SupInfo> map) {
        SupInfo supInfo = map.get("supInfo");
        StringBuilder param = new StringBuilder();
        StringBuilder value = new StringBuilder();
        param.append(" INSERT INTO `yuhuang_sup_info` ( `user_id` ");
        value.append(" #{supInfo.userId} ");
        if (StringUtils.isNotBlank(supInfo.getName())) {
            param.append("  ,`name` ");
            value.append("  ,#{supInfo.name} ");
        }
        if (StringUtils.isNotBlank(supInfo.getOpenId())) {
            param.append("  ,`open_id` ");
            value.append("  ,#{supInfo.openId} ");
        }
        if (StringUtils.isNotBlank(supInfo.getNickName())) {
            param.append("  ,`nick_name` ");
            value.append("  ,#{supInfo.nickName} ");
        }
        if (StringUtils.isNotBlank(supInfo.getPhone())) {
            param.append("  ,`phone` ");
            value.append("  ,#{supInfo.phone} ");
        }
        if (StringUtils.isNotBlank(supInfo.getIp())) {
            param.append("  ,`ip` ");
            value.append("  ,#{supInfo.ip} ");
        }
        if (StringUtils.isNotBlank(supInfo.getAddress())) {
            param.append("  ,`address` ");
            value.append("  ,#{supInfo.address} ");
        }
        if (StringUtils.isNotBlank(supInfo.getQq())) {
            param.append("  ,`qq` ");
            value.append("  ,#{supInfo.qq} ");
        }
        if (StringUtils.isNotBlank(supInfo.getWangwang())) {
            param.append("  ,`wangwang` ");
            value.append("  ,#{supInfo.wangwang} ");
        }
        if (StringUtils.isNotBlank(supInfo.getEmail())) {
            param.append("  ,`email` ");
            value.append("  ,#{supInfo.email} ");
        }
        if (StringUtils.isNotBlank(supInfo.getIdNumber())) {
            param.append("  ,`id_number` ");
            value.append("  ,#{supInfo.idNumber} ");
        }
        if (StringUtils.isNotBlank(supInfo.getIdType())) {
            param.append("  ,`id_type` ");
            value.append("  ,#{supInfo.idType} ");
        }
        if (StringUtils.isNotBlank(supInfo.getRemark())) {
            param.append("  ,`remark` ");
            value.append("  ,#{supInfo.remark} ");
        }
        if (StringUtils.isNotBlank(supInfo.getRealName())) {
            param.append("  ,`real_name` ");
            value.append("  ,#{supInfo.realName} ");
        }

        param.append(" ) VALUES ( ");
        value.append(" ) ");

        param.append(value.toString());

        return param.toString();
    }

    /**
     * 查询会员数量
     *
     * @param param 参数
     * @return sql
     */
    public String queryEquityCreditModifyCount(HashMap<String, Object> param) {
        StringBuffer sql = new StringBuffer(300);
        sql.append("SELECT COUNT(1)    ");
        commonEquitySql(sql, param);
        return sql.toString();
    }

    /**
     * 查询会员
     *
     * @param param 参数
     * @return sql
     */
    public String queryEquityCreditModify(HashMap<String, Object> param) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("SELECT mr.id,su.account,mr.original_credit originalCredit ,mr.new_credit newCredit,mr.admin,mr.remark,mr.modify_time modifyTime");
        commonEquitySql(sql, param);
        Page page = (Page) param.get("page");
        sql.append(" ORDER BY modify_time desc ");
        sql.append(" LIMIT ").append(page.getBeginIndex()).append(" , ").append(page.getSize());
        return sql.toString();
    }

    private void commonEquitySql(StringBuffer sql, HashMap<String, Object> param) {
        sql.append(" FROM `yuhuang_credit_modify_record` mr,yuhuang_sup_user su WHERE mr.userId=su.user_id  ");
        EquityCreditModify equityCreditModify = (EquityCreditModify) param.get("equityCreditModify");
        if (StringUtils.isNotBlank(equityCreditModify.getAccount())) {
            sql.append(" and instr(su.account,#{equityCreditModify.account})>0   ");
        }
        String startTime = (String) param.get("startTime");
        if (StringUtils.isNotBlank(startTime)) {
            sql.append(" AND mr.modify_time >=#{startTime}");
        }
        String endTime = (String) param.get("endTime");
        if (StringUtils.isNotBlank(endTime)) {
            sql.append(" AND mr.modify_time <=#{endTime}");
        }
    }
}
